﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DAL.Entities;

namespace DAL.EntitiesDAL
{
public class phieu_DAL:BaseDAL,UTL.IBaseDAL
    {
        public int Count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            string sql = @" SELECT * FROM PHIEU";
            return ExecuteQuery(sql);
        }

        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            string sql = @"DELETE FROM PHIEU WHERE maban = '{0}'";
            sql = string.Format(sql, id);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert(object obj)
        {
            var o = (phieu)obj;
            string sql = @"set dateformat dmy; INSERT INTO PHIEU(MAPHIEU, NGAYTAOPHIEU, MABAN, TINHTIEN) VALUES('{0}',N'{1}', N'{2}', '{3}') ";
            sql = string.Format(sql, o.MAPHIEU, o.NGAYTAOPHIEU, o.MABAN, o.TINHTIEN);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        

        public bool Update(object obj)
        {
            var o = (phieu)obj;
            string sql = @"SET dateformat dmy;UPDATE PHIEU SET NGAYTAOPHIEU=N'{1}',MABAN=N'{2}' , TINHTIEN= '{3}' WHERE MAPHIEU='{0}'";
            sql = string.Format(sql, o.MAPHIEU, o.NGAYTAOPHIEU, o.MABAN, o.TINHTIEN);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool doi_ban(string ban, string mp)
        {            
            string sql = @"SET dateformat dmy;UPDATE PHIEU SET MABAN= '{0}' WHERE MAPHIEU='{1}'";
            sql = string.Format(sql, ban, mp);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool gop_ban(string ban, string mp)
        {
            string sql = @"
                        SET dateformat dmy; UPDATE chitietphieu SET MAPHIEU = '{0}' WHERE MAPHIEU='{1}'
                        delete from phieu where  maphieu = '{1}'
                         ";
            sql = string.Format(sql, ban, mp);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable Search(string name)
        {
            throw new NotImplementedException();
        }

        public DataTable Select_Report(string stt)
        {
            /*string sql = @" SELECT a.STT_HDB, a.MA_SP, a.GIA_BAN, a.SO_LUONG, a.THANH_TIEN , MA_NV, MA_KH, VAT, TONG_TIEN, NGAY_LAP_HDB
                            FROM CHI_TIET_HOA_DON_BAN a, HOA_DON_BAN b
                            WHERE a.STT_HDB =b.STT_HDB and a.STT_HDB = '{0}' */
            string sql = @"SELECT A.MAPHIEU, TENBAN, NGAYTAOPHIEU, TENMON, SOLUONG, B.DONGIA, GIAMGIA,B.TINHTIEN, HOTEN, A.TINHTIEN as tong
                            FROM PHIEU A, CHITIETPHIEU B, THUCDON C, BAN D, NHANVIEN E
                            WHERE A.MAPHIEU = B.MAPHIEU
                            AND B.MAMON = C.MAMON
                            AND A.MABAN = D.MABAN
                            AND B.MANV = E.MANV 
                            AND A.MAPHIEU = '{0}'
                            ";
            sql = string.Format(sql, stt);
            return ExecuteQuery(sql);
        }

        public DataTable BAN()
        {
            var sql = @" select a.maban as id, a.tenban as ten from ban a where a.maban not in (select b.maban from phieu b)";
            return ExecuteQuery(sql);
        }
        public DataTable Select_max()
        {
            var sql = @"SELECT MAX(MAPHIEU) as id FROM PHIEU";
            return ExecuteQuery(sql);
        }

        public string Select_stt(string stt)
        {
            var sql = @"SELECT maphieu
                            FROM phieu
                            WHERE  maban= '{0}'";
            sql = string.Format(sql, stt);
            DataTable dt = ExecuteQuery(sql);
            return dt.Rows.Count > 0 ? dt.Rows[0]["maphieu"] + " " : "0";
        }

        public string sum(string add)
        {
            string sql = @"select sum(tinhtien)as tong
                            from chitietphieu
                            where maphieu = '{0}'";
            sql = string.Format(sql, add);
            DataTable dt = ExecuteQuery(sql);
            return dt.Rows.Count > 0 ? dt.Rows[0]["tong"] + " " : "0";
        }

        public bool xoa(string id)
        {
            string sql = @"
                            DELETE FROM CHITIETPHIEU WHERE MAPHIEU='{0}'
                            
                            DELETE FROM PHIEU WHERE MAPHIEU='{0}'
                           ";
            sql = string.Format(sql, id);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        


    }
}
